USE [RelightDB]
GO

/****** Object:  Table [dbo].[Users]    Script Date: 10/19/2011 00:15:06 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Users](
	[UserID] [int] IDENTITY(1,1) NOT NULL,
	[LoginID] [varchar](50) NOT NULL,
	[Password] [varchar](50) NOT NULL,
	[LastLoginDate] [datetime] NULL,
 CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED 
(
	[UserID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

---------------------------------------------------------------
--26 Oct 2011

USE [RelightDB]
GO

/****** Object:  Table [dbo].[Branches]    Script Date: 10/30/2011 12:08:46 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Branches](
	[BranchID] [tinyint] IDENTITY(1,1) NOT NULL,
	[BranchCode] [varchar](8) NOT NULL,
	[BranchName] [varchar](40) NOT NULL,
	[BranchAddress] [varchar](200) NULL,
	[BranchPhone1] [varchar](20) NULL,
	[BranchPhone2] [varchar](20) NULL,
	[EntryDate] [datetime] NOT NULL,
	[ModifyDate] [datetime] NOT NULL,
 CONSTRAINT [PK_Branches] PRIMARY KEY CLUSTERED 
(
	[BranchID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Branches] ADD  CONSTRAINT [DF_Branches_EntryDate]  DEFAULT (getdate()) FOR [EntryDate]
GO

ALTER TABLE [dbo].[Branches] ADD  CONSTRAINT [DF_Branches_ModifyDate]  DEFAULT (getdate()) FOR [ModifyDate]
GO

-----------------------------------------------------------------


USE [RelightDB]
GO

/****** Object:  Index [IX_Branches_UniqueBranchCode]    Script Date: 10/27/2011 00:32:02 ******/
CREATE UNIQUE NONCLUSTERED INDEX [IX_Branches_UniqueBranchCode] ON [dbo].[Branches] 
(
	[BranchCode] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

------------------------------------------------------------

USE [RelightDB]
GO

/****** Object:  Table [dbo].[Setup]    Script Date: 10/27/2011 00:03:49 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Setup](
	[ID] [tinyint] NOT NULL,
	[KeyName] [varchar](50) NOT NULL,
	[XmlValue] [xml] NULL,
	[IntValue] [int] NULL,
 CONSTRAINT [PK_Setup] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

-----------------------------------------------------------

insert into Setup(ID, KeyName, XmlValue, IntValue) 
values
(1, 
'BranchCode', 
'<Root>
  <Prefix>B</Prefix>
</Root>', 
0)

----------------------------------------------------------

insert into Setup(ID, KeyName, XmlValue, IntValue) 
values
(2, 
'MemberCode', 
'<Root>
  <Prefix>M</Prefix>
</Root>', 
0)

-------------------------------------------------------------------------------------------------------
-- Dipanzon - 06-Nov-2011


USE [RelightDB]
GO
/****** Object:  Table [dbo].[Members]    Script Date: 11/06/2011 01:11:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Members](
	[MemberID] [int] IDENTITY(1,1) NOT NULL,
	[MemberCode] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[IntroducerMemberCode] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[ApplicationFormNo] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[RankID] [tinyint] NOT NULL,
	[BranchID] [tinyint] NOT NULL,
	[FirstName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[MiddleName] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[LastName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[GurdianName] [varchar](120) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[GurdianType] [tinyint] NULL,
	[Address] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[PinCode] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[DateOfBirth] [datetime] NOT NULL,
	[DateOfJoining] [datetime] NOT NULL,
	[Sex] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[Qualification] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[NomineeName] [varchar](120) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[NomineeAge] [tinyint] NULL,
	[NomineeRelationship] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[BloodGroup] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Height] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Phone] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Mobile] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Email] [varchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[PanOrGirNo] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[LastUpdateUserID] [int] NULL,
	[EntryDate] [datetime] NOT NULL CONSTRAINT [DF_Members_EntryDate]  DEFAULT (getdate()),
	[ModifyDate] [datetime] NOT NULL CONSTRAINT [DF_Members_ModifyDate]  DEFAULT (getdate()),
 CONSTRAINT [PK_Members_1] PRIMARY KEY NONCLUSTERED 
(
	[MemberID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

--------------------------------------------------------------------------------------------------------------------------------------------------

USE [RelightDB]
GO
/****** Object:  Table [dbo].[Ranks]    Script Date: 11/06/2011 01:16:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Ranks](
	[RankID] [tinyint] IDENTITY(1,1) NOT NULL,
	[RankName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[RankPosition] [tinyint] NOT NULL,
	[RankDescription] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[LastUpdateUserID] [int] NULL,
	[EntryDate] [datetime] NOT NULL CONSTRAINT [DF_Ranks_EntryDate]  DEFAULT (getdate()),
	[ModifyDate] [datetime] NOT NULL CONSTRAINT [DF_Ranks_ModifyDate]  DEFAULT (getdate()),
 CONSTRAINT [PK_Ranks] PRIMARY KEY CLUSTERED 
(
	[RankID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

------------------------------------------------------------------------------------------------------------------------------------------------
--Dipankar
--Date: 9 Sep, 2011

USE [RelightDB]
GO
/****** Object:  Table [dbo].[ProductTypes]    Script Date: 11/09/2011 07:15:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ProductTypes](
	[ProductTypeID] [tinyint] IDENTITY(1,1) NOT NULL,
	[ProductType] [varchar](100) NOT NULL,
	[IsActive] [bit] NOT NULL,
 CONSTRAINT [PK_ProductTypes] PRIMARY KEY CLUSTERED 
(
	[ProductTypeID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
------------------------------------------

INSERT INTO [RelightDB].[dbo].[ProductTypes]
           ([ProductType]
           ,[IsActive])
     VALUES
           ('Installment For Advance Tour Booking'
           ,1)
GO
--------------------
INSERT INTO [RelightDB].[dbo].[ProductTypes]
           ([ProductType]
           ,[IsActive])
     VALUES
           ('One Time Tour Booking Package'
           ,1)
GO
---------------------
INSERT INTO [RelightDB].[dbo].[ProductTypes]
           ([ProductType]
           ,[IsActive])
     VALUES
           ('Project Profit Distribution'
           ,1)
GO
--------------------------------------------------------------------------------------------------------------------------------